﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Lswweb.IDAL;
using Lswweb.Model;
using Lswweb.Common;
using System.Linq;

namespace Lswweb.SqlServerDAL
{
    public class OrderDAL : IOrders
    {
        private Builder<OrderInfo> orderBuilder = new Builder<OrderInfo>("Orders", "Id");
        private OrderPaymentDAL paymentDAL = OrderPaymentDAL.PaymentDAL;
        private OrderLogisticsDAL logisticsDAL = OrderLogisticsDAL.LogisticsDAL;
        private OrderItemsDAL itemsDAL = OrderItemsDAL.ItemsDAL;

        public OrderDAL()
        {
            orderBuilder.GetParameters += new ParametersBuilder<OrderInfo>(GetParameters);
            orderBuilder.CreateModel += new ReaderBind<OrderInfo>(ReaderBind);
        }
        
        #region IOrders 成员

        public OrderInfo GetOrder(int orderId)
        {
            if (orderId <= 0)
                return null;
            OrderInfo info= orderBuilder.GetModel(orderId);
            if (info != null)
            {
                info.Logistics = logisticsDAL.GetLogistics(orderId);
                info.Payment = paymentDAL.GetPayment(orderId);
                info.OrderItems = itemsDAL.GetOrderItems(orderId);
            }
            return info;
        }

        public int Insert(OrderInfo info)
        {
            int orderId = orderBuilder.Insert(
                info,
                "UserId",
                "LogisticsPrices",
                "ProductPrices",
                "PreferentialPrices",
                "Name",
                "Phone",
                "Mobile",
                "Address",
                "Zip",
                "DateCreated",
                "Status",
                "Remark",
                "CityId",
                "CompanyName",
                "Insurance");


            if (orderId > 0)
            {
                if(info.OrderItems.Count > 0)
                    OrderItemsDAL.ItemsDAL.Insert(orderId, info.OrderItems);

                info.Logistics.OrderId = orderId;
                OrderLogisticsDAL.LogisticsDAL.Insert(info.Logistics);
            }

            return orderId;
        }

        public OrderStatus GetStatus(int orderId)
        {
            OrderStatus status=(OrderStatus)(-1);
            if(orderId<=0)
                return status;

            Object obj = orderBuilder.GetFieldValue("Status",orderId);
            if (obj != null)
                status= (OrderStatus)Convert.ToInt32(obj);

            return status;  
        }

        public IList<OrderItemInfo> GetOrderItems(int orderId)
        {
            return itemsDAL.GetOrderItems(orderId);
        }

        public bool Update(OrderInfo info)
        {
            return orderBuilder.Update(
                info,
                "Id=@Id",
                "Mobile",
                "Email",
                "Address",
                "Zip",
                "Remark",
                "LogisticsPrices",
                "ProductPrices",
                "PreferentialPrices",
                "Name",
                "Phone",
                "CityId",
                "Insurance");
        }

        public bool Update(int orderId, OrderStatus statusType)
        {
            if (orderId <= 0)
                return false;

            return orderBuilder.UpdateFields("Status", orderId, (int)statusType);
        }

        public bool UpdatePayment(PaymentInfo paymentInfo)
        {
            if (paymentInfo.OrderId <= 0)
                return false;

            if (paymentDAL.IsHas(paymentInfo.OrderId))
                return paymentDAL.Update(paymentInfo);
            else
                return Convert.ToBoolean(paymentDAL.Insert(paymentInfo));
        }

        public bool UpdateLogistics(LogisticsInfo logisticsInfo)
        {
            if (logisticsInfo.OrderId <= 0)
                return false;

            if (logisticsDAL.IsHas(logisticsInfo.OrderId))
                logisticsDAL.Update(logisticsInfo);
            else
                logisticsDAL.Insert(logisticsInfo);

            return true;
        }

        public IList<OrderInfo> GetOrders(int userId, OrderStatus status, int pageSize, int pageIndex, out int record)
        {
            StringBuilder where=new StringBuilder();
            IList<IDataParameter> parameter = new List<IDataParameter>();
            if (userId>0)
            {
                parameter.Add(DataAccess.CreateParameter("@UserId", userId));
                where.Append(" and UserId=@UserId");
            }

            if (Enum.IsDefined(typeof(OrderStatus), status))
            {
                where.Append(" and Status=@Status");
                parameter.Add(DataAccess.CreateParameter("Status",(int)status));
            }

            string where2 = where.ToString();
            if (!string.IsNullOrEmpty(where2))
                where2 = where2.Remove(0,5);

            IDataParameter[] parameters2 = new IDataParameter[parameter.Count];
            parameter.CopyTo(parameters2, 0);

            return orderBuilder.GetModels(string.Empty, where2, "Id desc", pageIndex, pageSize, out record, parameters2);
        }
        public IList<OrderInfo> GetOrders(string searchKey, Pair<DateTime?> dateTimeRange, OrderStatus? status, int pageSize, int pageIndex, out int record)
        {
            StringBuilder where = new StringBuilder();
            IList<IDataParameter> parameter = new List<IDataParameter>();

            if (!string.IsNullOrEmpty(searchKey))
            {
                parameter.Add(DataAccess.CreateParameter("@SearchKey",searchKey));

                where.Append(" and (Name=@SearchKey or Phone=@SearchKey or Mobile=@SearchKey or  exist(select Id from Users where LoginName=@searchKey))");
            }

            //maxDateTime条件
            if (dateTimeRange.Right != null)
            {
                parameter.Add(DataAccess.CreateParameter("@MaxDateTime", dateTimeRange.Right));
                where.Append(" and DateCreated <= @MaxDateTime");
            }

            //minDateTime条件
            if (dateTimeRange.Left != null)
            {
                parameter.Add(DataAccess.CreateParameter("@MinDateTime", dateTimeRange.Left));
                where.Append(" and DateCreated >= @MinDateTime");
            }

            if (status != null && Enum.IsDefined(typeof(OrderStatus), status))
            {
                where.Append(" and Status=@Status");
                parameter.Add(DataAccess.CreateParameter("Status", (int)status));

            }

            string where2 = where.ToString();
            if (!string.IsNullOrEmpty(where2))
                where2 = where2.Remove(0, 5);

            IDataParameter[] parameters2 = new IDataParameter[parameter.Count];
            parameter.CopyTo(parameters2, 0);

            return orderBuilder.GetModels(string.Empty, where2, "Id desc", pageIndex, pageSize, out record, parameters2);
        }

        public bool Delete(int orderId)
        {
            if (orderId <= 0)
                return false;

            return orderBuilder.Delete(orderId);
        }

        public int GetNotEndOrderNumber(int userId)
        {
            string sql = string.Format("select count(Id) from orders where UserId=@UserId and Status<{0}", (int)OrderStatus.ConfirmationReceipt);
            object obj = DataAccess.ExecuteScalar(sql, DataAccess.CreateParameter("@UserId", userId));
            if (obj!=null && obj!=DBNull.Value)
                return Convert.ToInt32(obj);
            else
                return 0;
        }

        public int GetEndOrderNumber(int userId)
        {
            string sql = string.Format("select count(Id) from orders where UserId=@UserId and Status={0}", (int)OrderStatus.ConfirmationReceipt);
            object obj =DataAccess.ExecuteScalar(sql, DataAccess.CreateParameter("@UserId", userId));
            if (obj != null && obj != DBNull.Value)
                return Convert.ToInt32(obj);
            else
                return 0;
        }

        public decimal GetBuyTotalMoney(int userId, Pair<DateTime?> dateTimeRange)
        {
            string sql = string.Format("select sum(ProductPrices) + sum(LogisticsPrices) from Orders where UserId=@UserId and Status={0}", (int)OrderStatus.ConfirmationReceipt);
            if (dateTimeRange.Left != null)
                sql += " and DateCreated >= @MinDateCreated";

            if (dateTimeRange.Right != null)
                sql += " and DateCreated <= @MaxDateCreated";

            object obj = DataAccess.ExecuteScalar(
                sql,
                DataAccess.CreateParameter("@UserId", userId),
                DataAccess.CreateParameter("@MinDateCreated", dateTimeRange.Left),
                DataAccess.CreateParameter("@MaxDateCreated", dateTimeRange.Right));

            if (obj != null && obj != DBNull.Value)
                return Convert.ToDecimal(obj);
            else
                return 0;
        }

        public IList<ProductInfo> GetSalesRanking(Pair<DateTime> dateTimeRang, int topNumber)
        {
            IList<ProductInfo> products = new List<ProductInfo>();

            string sql = "select top(" + topNumber + ") ProductId,sum(Quantity) as Quantity from OrderItems where exists(select OrderId from Orders where Orders.DateCreated>= @MinDate and Orders.DateCreated<=@MaxDate) group by ProductId order by Quantity desc;";

            IProducts productDAL = DALFactory.CreateProductDAL();
            using (IDataReader reader = DataAccess.ExecuteReader(
                sql, 
                DataAccess.CreateParameter("@MinDate", dateTimeRang.Left),
                DataAccess.CreateParameter("@MaxDate", dateTimeRang.Right)))
            {
                while (reader.Read())
                {
                    int productId = reader["ProductId"].As<int>();
                    int quantity = reader["Quantity"].As<int>();


                    ProductInfo info = productDAL.GetProduct(productId);
                    if (info != null)
                    {
                        info.Sale = quantity;
                        products.Add(info);
                    }
                }
            }

            return products;
        }

        public OrderItemInfo GetFirstOrderItem(int orderId)
        {
            return OrderItemsDAL.ItemsDAL.GetFirstItem(orderId);
        }

        #endregion

        #region Protected Methods

        protected IDataParameter[] GetParameters(OrderInfo orderInfo)
        {
            return new IDataParameter[]{
                DataAccess.CreateParameter("@Id", orderInfo.Id),
                DataAccess.CreateParameter("@UserId", orderInfo.UserId),
                DataAccess.CreateParameter("@DateCreated", orderInfo.DateCreated),
                DataAccess.CreateParameter("@LogisticsPrices", orderInfo.LogisticsPrices),
                DataAccess.CreateParameter("@ProductPrices", orderInfo.ProductPrices),
                DataAccess.CreateParameter("@PreferentialPrices", orderInfo.PreferentialPrices),
                DataAccess.CreateParameter("@Name", orderInfo.Name),
                DataAccess.CreateParameter("@Phone", orderInfo.Phone),
                DataAccess.CreateParameter("@Mobile", orderInfo.Mobile),
                DataAccess.CreateParameter("@Status", (int)orderInfo.Status),
                DataAccess.CreateParameter("@Address", orderInfo.Address),
                DataAccess.CreateParameter("@Zip", orderInfo.Zip),
                DataAccess.CreateParameter("@LtsType",orderInfo.LtsType),
                DataAccess.CreateParameter("@Remark", orderInfo.Remark),
                DataAccess.CreateParameter("@CityId", orderInfo.CityId),
                DataAccess.CreateParameter("@CompanyName",orderInfo.CompanyName),
                DataAccess.CreateParameter("@Insurance",orderInfo.Insurance)
            };
        }

        protected OrderInfo ReaderBind(Hashtable dataReader)
        {
            OrderInfo model = new OrderInfo();

            model.Id = dataReader.Get<int>("Id");
            model.LogisticsPrices = dataReader.Get<decimal>("LogisticsPrices");
            model.ProductPrices = dataReader.Get<decimal>("ProductPrices");
            model.PreferentialPrices = dataReader.Get<decimal>("PreferentialPrices");
            model.DateCreated = dataReader.Get<DateTime>("DateCreated");
            model.Name = dataReader.Get<string>("Name");
            model.Phone = dataReader.Get<string>("Phone");
            model.Mobile = dataReader.Get<string>("Mobile");
            model.UserId = dataReader.Get<int>("UserId");
            model.Address = dataReader.Get<string>("Address");
            model.Zip = dataReader.Get<string>("Zip");
            model.Status = dataReader.Get<OrderStatus>("Status");
            model.Remark = dataReader.Get<string>("Remark");
            model.LtsType = dataReader.Get<int>("LtsType");
            model.CityId = dataReader.Get<int>("CityId");
            model.CompanyName = dataReader.Get<string>("CompanyName");
            model.Insurance = dataReader.Get<decimal>("Insurance");

            return model;
        }

        #endregion
    }

    public class OrderItemsDAL
    {

        private Builder<OrderItemInfo> itemBuilder = new Builder<OrderItemInfo>("OrderItems", "OrderId");

        private OrderItemsDAL() 
        {
            itemBuilder.CreateModel += new ReaderBind<OrderItemInfo>(CreateModel);
            itemBuilder.GetParameters += new ParametersBuilder<OrderItemInfo>(GetParameters);
        }

        private static OrderItemsDAL orderItems;
        public static OrderItemsDAL ItemsDAL
        {
            get
            {
                if (orderItems == null)
                    orderItems = new OrderItemsDAL();
                return orderItems;
            }
        }

        public IList<OrderItemInfo> GetOrderItems(int orderId)
        {
            int total;
            return itemBuilder.GetModels(string.Empty, "OrderId=@OrderId", "OrderId asc", 1, 999999, out total, DataAccess.CreateParameter("OrderId", orderId));
        }

        public OrderItemInfo GetFirstItem(int orderId)
        {
            OrderItemInfo orderItem=null;
            string sql="select top 1 * from OrderItems where OrderId=@OrderId;";
            using (IDataReader reader = DataAccess.ExecuteReader(sql, DataAccess.CreateParameter("@OrderId", orderId)))
            {
                if(reader.Read())
                    orderItem = itemBuilder.GetModel(reader);
            }

            return orderItem;
        }

        public void Insert(int orderId,IList<OrderItemInfo> items)
        {
            foreach (OrderItemInfo itemInfo in items)
            {
                if (itemInfo.OrderId <= 0)
                {
                    if (orderId <= 0)
                        continue;
                    else
                        itemInfo.OrderId = orderId;
                }

                itemBuilder.Insert(
                    itemInfo,
                    "OrderId",
                    "ProductId",
                    "Quantity",
                    "Price",
                    "Others");
            }
        }

        public bool Delete(int orderId)
        {
            if (orderId <= 0)
                return false;

            return itemBuilder.Delete(
                "OrderId=@OrderId",
                DataAccess.CreateParameter("OrderId",orderId));
        }

        #region Protected Methods

        protected IDataParameter[] GetParameters(OrderItemInfo t)
        {
            return new IDataParameter[] {
                    DataAccess.CreateParameter("@OrderId",t.OrderId),
                    DataAccess.CreateParameter("@ProductId",t.ProductId),
                    DataAccess.CreateParameter("@Quantity",t.Quantity),
                    DataAccess.CreateParameter("@Price",t.Price),
                    DataAccess.CreateParameter("@Others",t.Others.CombinationToString())};
        }

        protected OrderItemInfo CreateModel(Hashtable reader)
        {
            OrderItemInfo itemInfo = new OrderItemInfo(
                reader.Get<int>("ProductId"),
                reader.Get<int>("Quantity"),
                reader.Get<Decimal>("Price"));

            itemInfo.OrderId = reader.Get<int>("OrderId");

            string otherString = reader.Get<string>("Others");
            otherString.SplitHandler(
                delegate(string item,int index)
                {
                    string[] itemPair = item.Split('=');
                    itemInfo.Others.Add(itemPair[0], itemPair[1]);
                });

            return itemInfo;
        }

        #endregion
    }

    public class OrderPaymentDAL
    {
        private Builder<PaymentInfo> paymentBuilder = new Builder<PaymentInfo>("OrderPayment", "OrderId");

        private OrderPaymentDAL() 
        { 
            paymentBuilder.CreateModel+=new ReaderBind<PaymentInfo>(CreateModel);
            paymentBuilder.GetParameters+=new ParametersBuilder<PaymentInfo>(GetParameters);
        }

        private static OrderPaymentDAL paymentDAL;
        public static OrderPaymentDAL PaymentDAL
        {
            get
            {
                if (paymentDAL == null)
                    paymentDAL = new OrderPaymentDAL();
                return paymentDAL;
            }
        }

        public PaymentInfo GetPayment(int orderId)
        {
            if (orderId <= 0)
                return null;

            return paymentBuilder.GetModel(orderId);
        }

        public int Insert(PaymentInfo paymentInfo)
        {
            try
            {
                paymentBuilder.Insert(
                   paymentInfo,
                   "OrderId",
                   "BankName",
                   "BankAccounts",
                   "CurrencyType",
                   "Amount",
                   "Dates",
                   "Remark",
                   "DateCreated");

                return paymentInfo.OrderId;
            }
            catch(System.Data.DataException)
            {
                return 0;
            }
        }

        public bool IsHas(int orderId)
        {
            return paymentBuilder.IsHas(orderId);
        }

        public bool Update(PaymentInfo paymentInfo)
        {
            return paymentBuilder.Update(
                paymentInfo,
                "OrderId=@OrderId",
                "BankName",
                "BankAccounts",
                "CurrencyType",
                "Amount",
                "Dates",
                "Remark",
                "DateCreated");
        }

        #region Protected Methods

        protected IDataParameter[] GetParameters(PaymentInfo model)
        {
            return new IDataParameter[]{
                DataAccess.CreateParameter("OrderId",model.OrderId),
                DataAccess.CreateParameter("BankName",model.BankName),
                DataAccess.CreateParameter("BankAccounts",model.BankAccounts),
                DataAccess.CreateParameter("CurrencyType",model.CurrencyType),
                DataAccess.CreateParameter("Amount",model.Amount),
                DataAccess.CreateParameter("Dates",model.Dates),
                DataAccess.CreateParameter("Remark",model.Remark),
                DataAccess.CreateParameter("DateCreated",model.DateCreated)
            };
        }

        protected PaymentInfo CreateModel(Hashtable reader)
        {
            return new PaymentInfo()
            {
                OrderId = reader.Get<int>("OrderId"),
                BankName = reader.Get<string>("BankName"),
                BankAccounts = reader.Get<string>("BankAccounts"),
                CurrencyType = reader.Get<string>("CurrencyType"),
                Amount = reader.Get<decimal>("Amount"),
                Dates = reader.Get<DateTime>("Dates"),
                Remark = reader.Get<string>("Remark"),
                DateCreated = reader.Get<DateTime>("DateCreated"),
            };
        }

        #endregion
    }

    public class OrderLogisticsDAL
    {
        private Builder<LogisticsInfo> logisticsBuilder = new Builder<LogisticsInfo>("Logistics", "OrderId");

        private OrderLogisticsDAL()
        {
            logisticsBuilder.CreateModel += new ReaderBind<LogisticsInfo>(CreateModel);
            logisticsBuilder.GetParameters += new ParametersBuilder<LogisticsInfo>(GetParameters);
        }

        private static OrderLogisticsDAL logisticsDAL;
        public static OrderLogisticsDAL LogisticsDAL
        {
            get
            {
                if (logisticsDAL == null)
                    logisticsDAL = new OrderLogisticsDAL();
                return logisticsDAL;
            }
        }

        public LogisticsInfo GetLogistics(int orderId)
        {
            if (orderId <= 0)
                return null;

            return logisticsBuilder.GetModel(orderId);
        }

        public bool IsHas(int orderId)
        {
            return logisticsBuilder.IsHas(orderId);
        }

        public void Insert(LogisticsInfo logisticsInfo)
        {
            logisticsBuilder.Insert(
                logisticsInfo,
                "OrderId",
                "LtsType",
                "Name",
                "TrackingNumber",
                "DateCreated",
                "SendDates",
                "Remark");
        }

        public bool Update(LogisticsInfo logsticsInfo)
        {
            return logisticsBuilder.Update(
                logsticsInfo,
                "OrderId=@OrderId",
                "LtsType",
                "Name",
                "TrackingNumber",
                "SendDates",
                "Remark");
        }

        #region Protected Methods

        protected IDataParameter[] GetParameters(LogisticsInfo model)
        {
            return new IDataParameter[]{
                DataAccess.CreateParameter("@OrderId",model.OrderId),
                DataAccess.CreateParameter("@LtsType",model.LtsType),
                DataAccess.CreateParameter("@Name",string.IsNullOrEmpty(model.Name)?null:model.Name),
                DataAccess.CreateParameter("@TrackingNumber",string.IsNullOrEmpty(model.TrackingNumber)?null:model.TrackingNumber),
                DataAccess.CreateParameter("@SendDates",model.SendDates),
                DataAccess.CreateParameter("@Remark",string.IsNullOrEmpty(model.Remark)?null:model.Remark),
                DataAccess.CreateParameter("@DateCreated",model.DateCreated),
            };
        }

        protected LogisticsInfo CreateModel(Hashtable reader)
        {
            return new LogisticsInfo()
            {
                OrderId = reader.Get<int>("OrderId"),
                LtsType = reader.Get<int>("LtsType"),
                Name = reader.Get<string>("Name"),
                TrackingNumber = reader.Get<string>("TrackingNumber"),
                DateCreated = reader.Get<DateTime>("DateCreated"),
                SendDates = reader.Get<DateTime?>("SendDates"),
                Remark = reader.Get<string>("Remark")
            };
        }

        #endregion
    }
}
